package cn.com.mjsoft.framework.persistence.core;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.log4j.Logger;

import cn.com.mjsoft.framework.exception.FrameworkException;

/**
 * JDBC操作工具类，封装了一些JDBC操作功能。这些功能多是为其他类服务，不建议直接使用这些方法
 * 
 * @author mjsoft
 * 
 */
public final class JdbcInstrument
{
    private static final Logger log = Logger.getLogger( JdbcInstrument.class );

    private static final String INFORMIX_DB = "Informix";

    private static Pattern updateParamPattern = Pattern.compile( "\\s{1}\\S+\\s*=" );

    private static Pattern insertPattern = Pattern.compile( "\\s*(?i)insert\\s{1,}into\\s{1,}.+" );

    private static Pattern insertParamPattern = Pattern.compile( "\\w+\\s*[,)]" );

    private JdbcInstrument()
    {

    }

    /**
     * 在指定的指定PreparedStatement中根据参数位置及其他条件设定其JAVA类型的值，对NULL类型会单独处理。
     * 
     * @param ps JDBC PreparedStatement
     * @param paramIndex paramIndex 参数位置
     * @param ps参数值
     * @throws SQLException
     */
    public static void setPreparedStatementParameterValue( PreparedStatement ps, int paramIndex,
        Object psValue ) throws SQLException
    {
        if( psValue != null )
        {
            setJavaTypeValue( ps, paramIndex, psValue );
        }
        else if( psValue == null )
        {
            // 设定空值到数据库
            boolean isInformix = false;
            try
            { // 针对informix的null值特定情况
                isInformix = ( ps.getConnection().getMetaData().getDatabaseProductName()
                    .indexOf( INFORMIX_DB ) != -1 );
            }
            catch ( Throwable ex )
            {
                log.debug( "无法得到当前数据库系统信息", ex );
            }

            if( isInformix )
            {
                ps.setObject( paramIndex, null );
            }
            else
            {
                ps.setNull( paramIndex, Types.NULL );
            }
        }
    }

    /**
     * 在指定的指定PreparedStatement中根据参数位置设定其JAVA类型的值，不包括NULL类型。
     * 
     * @param ps JDBC PreparedStatement
     * @param paramIndex 参数位置
     * @param psValue 参数值
     * @throws SQLException
     */
    public static void setJavaTypeValue( PreparedStatement ps, int paramIndex, Object psValue )
        throws SQLException
    {
        if( psValue instanceof String )
        {
            ps.setString( paramIndex, ( String ) psValue );
        }
        else if( psValue instanceof Integer )
        {
            ps.setInt( paramIndex, ( ( Integer ) psValue ).intValue() );
        }
        else if( psValue instanceof Long )
        {
            ps.setLong( paramIndex, ( ( Long ) psValue ).longValue() );
        }
        else if( psValue instanceof Float )
        {
            ps.setFloat( paramIndex, ( ( Float ) psValue ).floatValue() );
        }
        else if( psValue instanceof Short )
        {
            ps.setShort( paramIndex, ( ( Short ) psValue ).shortValue() );
        }
        else if( psValue instanceof Double )
        {
            ps.setDouble( paramIndex, ( ( Double ) psValue ).doubleValue() );
        }
        else if( psValue instanceof Boolean )
        {
            ps.setBoolean( paramIndex, ( ( Boolean ) psValue ).booleanValue() );
        }
        else if( ( psValue instanceof java.util.Date )
            && !( psValue instanceof java.sql.Date || psValue instanceof Time || psValue instanceof Timestamp ) )
        {
            ps.setTimestamp( paramIndex,
                new java.sql.Timestamp( ( ( java.util.Date ) psValue ).getTime() ) );
        }
        else if( psValue instanceof Calendar )
        {
            ps.setTimestamp( paramIndex, new Timestamp( ( ( Calendar ) psValue ).getTime()
                .getTime() ) );
        }
        else if( psValue instanceof java.sql.Date )
        {
            ps.setDate( paramIndex, ( java.sql.Date ) psValue );
        }
        else if( psValue instanceof Byte )
        {
            ps.setByte( paramIndex, ( ( Byte ) psValue ).byteValue() );
        }
        else if( psValue instanceof byte[] )
        {
            ps.setBytes( paramIndex, ( byte[] ) psValue );
        }
        else if( psValue instanceof BigDecimal )
        {
            ps.setBigDecimal( paramIndex, ( BigDecimal ) psValue );
        }
        else if( psValue instanceof Blob )
        {
            ps.setBlob( paramIndex, ( Blob ) psValue );
        }
        else if( psValue instanceof Clob )
        {
            ps.setClob( paramIndex, ( Clob ) psValue );
        }
        else if( psValue instanceof InputStream )
        {
            InputStream ins = ( InputStream ) psValue;
            try
            {
                ps.setBinaryStream( paramIndex, ins, ins.available() );
            }
            catch ( IOException e )
            {
                log.error( e );
                e.printStackTrace();
            }
        }
        else if( psValue instanceof CustomTypeValue )
        {
            ( ( CustomTypeValue ) psValue ).setTypeValue( ps, paramIndex );
        }
        else
        {
            ps.setObject( paramIndex, psValue );
        }
    }

    /**
     * 试图返回指定类型的结果
     * 
     * @param rs
     * @param index
     * @param type
     * @return
     * @throws SQLException
     * 
     */
    public static Object getResultSetColumnValue( ResultSet rs, int index, Class<?> type )
        throws SQLException
    {
        if( type != null )
        {
            Object value = null;
            boolean isSqlNull = false;// 某些类型SQl NULL判断

            if( String.class.equals( type ) )
            {
                value = rs.getString( index );
            }
            else if( Boolean.class.equals( type ) )
            {
                value = ( rs.getBoolean( index ) ? Boolean.TRUE : Boolean.FALSE );
                isSqlNull = true;
            }
            else if( Byte.class.equals( type ) )
            {
                value = new Byte( rs.getByte( index ) );
                isSqlNull = true;
            }
            else if( Short.class.equals( type ) )
            {
                value = new Short( rs.getShort( index ) );
                isSqlNull = true;
            }
            else if( Integer.class.equals( type ) )
            {
                value = new Integer( rs.getInt( index ) );
                isSqlNull = true;
            }
            else if( Long.class.equals( type ) )
            {
                value = new Long( rs.getLong( index ) );
                isSqlNull = true;
            }
            else if( Float.class.equals( type ) )
            {
                value = new Float( rs.getFloat( index ) );
                isSqlNull = true;
            }
            else if( Double.class.equals( type ) || Number.class.equals( type ) )
            {
                value = new Double( rs.getDouble( index ) );
                isSqlNull = true;
            }
            else if( byte[].class.equals( type ) )
            {
                value = rs.getBytes( index );
            }
            else if( java.sql.Date.class.equals( type ) )
            {
                value = rs.getDate( index );
            }
            else if( java.sql.Time.class.equals( type ) )
            {
                value = rs.getTime( index );
            }
            else if( java.sql.Timestamp.class.equals( type ) || java.util.Date.class.equals( type ) )
            {
                value = rs.getTimestamp( index );
            }
            else if( BigDecimal.class.equals( type ) )
            {
                value = rs.getBigDecimal( index );
            }
            else if( Blob.class.equals( type ) )
            {
                value = rs.getBlob( index );
            }
            else if( Clob.class.equals( type ) )
            {
                value = rs.getClob( index );
            }
            else
            {
                value = rs.getObject( index );
            }

            /**
             * 对于有可能返回NULL的类型进行处理，ResultSet的wasNull：<br>
             * 如果最后一个读取的列值为 SQL NULL，则返回 true；否则返回 false.那么结合以前<br>
             * 取的值，就可以判断是否返回的是SQL NULL
             */
            if( isSqlNull && value != null && rs.wasNull() )
            {
                value = null;
            }
            return value;
        }
        else
        {
            return getResultSetValue( rs, index );
        }
    }

    /**
     * 返回指定ResultSet中index位置的值
     * 
     * @param rs
     * @param index
     * @return
     * @throws SQLException
     * 
     */
    public static Object getResultSetValue( ResultSet rs, int index ) throws SQLException
    {
        Object obj = rs.getObject( index );
        if( obj instanceof Blob )
        {
            obj = rs.getBytes( index );
        }
        else if( obj instanceof Clob )
        {
            obj = rs.getString( index );
        }
        else if( obj != null && obj.getClass().getName().startsWith( "oracle.sql.TIMESTAMP" ) )
        {
            obj = rs.getTimestamp( index );
        }
        else if( obj != null && obj.getClass().getName().startsWith( "oracle.sql.DATE" ) )
        {
            String metaDataClassName = rs.getMetaData().getColumnClassName( index );
            if( "java.sql.Timestamp".equals( metaDataClassName )
                || "oracle.sql.TIMESTAMP".equals( metaDataClassName ) )
            {
                obj = rs.getTimestamp( index );
            }
            else
            {
                obj = rs.getDate( index );
            }
        }
        else if( obj != null && obj instanceof java.sql.Date )
        {
            if( "java.sql.Timestamp".equals( rs.getMetaData().getColumnClassName( index ) ) )
            {
                obj = rs.getTimestamp( index );
            }
        }
        return obj;
    }

    /**
     * 关闭一个Connection
     * 
     * @param conn
     * @return 返回true意味着关闭Connection成功
     */
    public static boolean closeConnection( Connection conn )
    {
        boolean isCloseSucc = false;
        if( conn != null )
        {
            try
            {
                conn.close();
                conn = null;
                log.debug( "释放connection成功" );
                isCloseSucc = true;
            }
            catch ( SQLException e )
            {
                isCloseSucc = false;
                log.warn( "无法释放connection", e );
            }
            catch ( Exception e )
            {
                isCloseSucc = false;
                log.warn( "未知异常出现，无法释放connection", e );
            }
        }
        return isCloseSucc;
    }

    /**
     * 关闭Statement
     * 
     * @param stmt
     */
    public static void closeStatement( Statement stmt )
    {
        if( stmt != null )
        {
            try
            {
                stmt.close();
                stmt = null;
                log.debug( "释放statment成功" );

            }
            catch ( SQLException e )
            {
                log.warn( "无法释放statment", e );
            }
            catch ( Exception e )
            {
                log.warn( "未知异常出现，无法释放statment", e );
            }
        }
    }

    /**
     * 关闭ResultSet
     * 
     * @param stmt
     */
    public static void closeResultSet( ResultSet rs )
    {
        if( rs != null )
        {
            try
            {
                rs.close();
                rs = null;
                log.debug( "释放Result成功" );
            }
            catch ( SQLException e )
            {
                log.warn( "无法释放ResultSet", e );
            }
            catch ( Exception e )
            {
                log.warn( "未知异常出现，无法释放ResultSet", e );
            }
        }
    }

    public static Object[] disposeSqlParamArgs( String sql, Object bean )
    {
        boolean insertMode = false;
        if( insertPattern.matcher( sql ).matches() )
        {
            insertMode = true;
        }

        String param = null;
        String paramName = null;

        Matcher matcherResult = null;
        if( insertMode )
        {
            matcherResult = insertParamPattern.matcher( sql );
        }
        else
        {
            matcherResult = updateParamPattern.matcher( sql );
        }

        List<Object> paramList = new ArrayList<Object>();
        Class<?> targetClazz = bean.getClass();
        Field field = null;
        try
        {
            while ( matcherResult.find() )
            {
                param = matcherResult.group();

                if( insertMode )
                {
                    paramName = param.replace( ",", "" ).replace( ")", "" );
                }
                else
                {
                    paramName = param.replace( "=", "" );
                }

                field = targetClazz.getDeclaredField( paramName.trim() );
                field.setAccessible( true );

                paramList.add( field.get( bean ) );
            }
        }
        catch ( Exception e )
        {
            if( e instanceof NoSuchFieldException )
            {
                throw new FrameworkException( "update语句中对应的字段 [" + paramName + "]不存在bean中,"
                    + bean.getClass() );
            }
            else
            {
                e.printStackTrace();
            }
        }

        return paramList.toArray();
    }

    public static Object[] disposeSqlParamArgsForMap( String sql, Map<String, Object> paramMap )
    {
        boolean insertMode = false;
        if( insertPattern.matcher( sql ).matches() )
        {
            insertMode = true;
        }

        String param = null;
        String paramName = null;

        Matcher matcherResult = null;
        if( insertMode )
        {
            matcherResult = insertParamPattern.matcher( sql );
        }
        else
        {
            matcherResult = updateParamPattern.matcher( sql );
        }

        List<Object> paramList = new ArrayList<Object>();

        while ( matcherResult.find() )
        {
            param = matcherResult.group();

            if( insertMode )
            {
                paramName = param.replace( ",", "" ).replace( ")", "" );
            }
            else
            {
                paramName = param.replace( "=", "" );
            }

            paramList.add( paramMap.get( paramName.trim() ) );
        }

        return paramList.toArray();
    }

}
